if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_ot_top')
          and type in ('P','PC'))
   drop procedure bd_proc_ot_top
go


create procedure bd_proc_ot_top (@date_begin DATE,@date_end DATE,@depts VARCHAR(MAX) = '',@showSize int = 10,@topType int = 1) as
BEGIN
	--加班小时TOP
	--思路：1、创建临时表，
				--2、将按条件查询的信息放入临时表temp_bd_kq_timecard（@date_begin，@date_end，@depts）
				--3、根据dept_id,user_serial聚合数据，并插入到#returnTable
				--4、执行sql，结果集返回（使用row_number() 获取部门内前@showSize条的记录数）

	--声明并初始化变量
	DECLARE @orderColumnName varchar(50)--排序字段名，对应@topType，临时表#returnTable

	set @orderColumnName = case @topType when 1 then 'ot_normal_hours'
																			when 2 then 'ot_weekend_hours'
																			when 3 then 'ot_holiday_hours'
																			when 4 then 'total_hours'
																			when 5 then 'total_coefficient_hours'
																			else 'ot_normal_hours' end;

	--1创建临时表
	create table #returnTable(
		dep_serial bigint,
		dep_no varchar(20),
		dep_name varchar(200) COLLATE Chinese_PRC_CI_AS,
		user_serial bigint,
		user_no varchar(50) COLLATE Chinese_PRC_CI_AS,
		user_name varchar(50) COLLATE Chinese_PRC_CI_AS,
		ot_normal_hours decimal(18,2),--平时加班
		ot_weekend_hours decimal(18,2),--休息日加班
		ot_holiday_hours decimal(18,2),--节假日加班
		total_hours decimal(18,2), --未加成系数的合计
		total_coefficient_hours decimal(18,2),--加成系数之后的合计

	)

	--2将参与统计的数据放入#temp_bd_kq_timecard
	SELECT * into #temp_bd_kq_timecard  FROM  bd_kq_timecard
	WHERE dept_id in (select * from string_split(@depts,',')) and card_date BETWEEN @date_begin and @date_end
	and ot_pending_hours > 0


	--3,汇总统计到#returnTable
	insert into #returnTable
	select
		d.dep_serial,
		d.dep_no,
		d.dep_name,
		u.user_serial,
		u.user_no,
		u.user_lname,
		x.ot_normal_hours/60.0,
		x.ot_weekend_hours/60.0,
		x.ot_holiday_hours/60.0,
		x.total_hours/60.0,
		x.total_coefficient_hours/60.0
	from
		(SELECT
			dept_id,
			user_serial,
			sum(ot_normal_hours) as ot_normal_hours,
			sum(ot_weekend_hours) as ot_weekend_hours,
			sum(ot_holiday_hours) as ot_holiday_hours,
			sum(ot_normal_hours)+sum(ot_weekend_hours)+sum(ot_holiday_hours) as total_hours,
			sum(ot_normal_hours)*1.5+sum(ot_weekend_hours)*2+sum(ot_holiday_hours)*3 as total_coefficient_hours
			from #temp_bd_kq_timecard
			group by dept_id,user_serial
		) x
		inner join dt_user u on u.user_serial = x.user_serial
		INNER JOIN dt_dep d on d.dep_serial = x.dept_id

	--4、执行sql，结果集返回
	DECLare @sql  varchar(200)='';
	set @sql =concat('SELECT * FROM(SELECT ROW_NUMBER() OVER (PARTITION BY dep_serial ORDER BY ',@orderColumnName,
		' DESC,user_no asc) rowid,* FROM #returnTable) TT WHERE TT.rowid <= ',@showSize)

	exec(@sql)

END
go
